<?php

/*
* Class teia\webdev\php\core\MySqlLink
* 
*
* @author Bernardo Quibiana
* @date created: 
*
* @date modified: 
* @modified: Bernardo Quibiana
*/

// namespace declaration: Core Abstract Data Types
namespace teia\webdev\php\core;

class SqlUtil {
	
	static function query_select($cols, $db_tb, $conds, $limit=0, $op='=', $rel='AND') {
		$col_str = FunctionPool::array_to_str($cols, ', ');
		if (empty($col_str)) $col_str = '*';
		$where = self::where_clause_conds($conds, $op, $rel);
		$cond_str = empty($where) ? '' : "WHERE $where"; 
		$limit_str = $limit > 0 ? "LIMIT $limit" : '';
		
		$query = "SELECT $col_str FROM $db_tb $cond_str $limit_str";
		
		return $query;
	}
	
	static function query_avg($db_tb, $conds=NULL, $exprs=NULL, $distinct=FALSE) {
		$where = self::where_clause_conds($conds, $op, $rel);
		$cond_str = empty($where) ? '' : "WHERE $where";
		if (is_null($expr)) {
			$expr_str = "*";
		} else {
			$expr_list = FunctionPool::array_to_str($exprs, ', ', '');
			$expr_str =  $distinct ? "DISTINCT " : '' . $expr_list;
		}
		$query = "SELECT AVG($expr_str) FROM $db_tb $cond_str";
	
		return $query;
	}
	
	static function query_count($db_tb, $conds=NULL, $exprs=NULL, $distinct=FALSE) {
		$where = self::where_clause_conds($conds, $op, $rel);
		$cond_str = empty($where) ? '' : "WHERE $where";
		if (is_null($expr)) {
			$expr_str = "*";
		} else {
			$expr_list = FunctionPool::array_to_str($exprs, ', ', '');
			$expr_str =  $distinct ? "DISTINCT " : '' . $expr_list;
		}
		$query = "SELECT COUNT($expr_str) FROM $db_tb $cond_str";
		
		return $query;
	}
	
	static function query_insert($db_tb, $data, $conds = null, $op='=', $rel='AND') {
		$data_str = self::insert_data($data);
		$where = self::where_clause_conds($conds, $op, $rel);
		$cond_str = empty($where) ? '' : "WHERE $where";
		$query = "INSERT INTO $db_tb $data_str $conds_str";
		
		return $query;
	}
	
	static function insert_data($data) {
		$key_str = FunctionPool::array_to_str(array_keys($data), ', ', '');
		$val_str = FunctionPool::array_to_str($data, ', ', "'");
		
		return "($key_str) values($val_str)";
	}
	
	static function where_clause($conjuction, $disjunction, $op_conj='=', $op_disj='=') {
		$cond_and = self::where_clause_conds($conjuction, $op_conj, 'AND');
		$cond_or = self::where_clause_conds($disjunction, $op_disj, 'OR');
		if (empty($cond_and) || empty($cond_or)) {
			// one or both are empty
			return $cond_and . $cond_or;
		} else {
			// none is empty
			return "$cond_and AND ($cond_or)";
		}
	}
	
	static function where_clause_conds($conds, $op='=', $rel='AND') {
		$str = "";
		$rel_str = " $rel ";
		foreach ($conds as $key => $value) {
			if (is_array($value)) {
				$val_str = self::where_clause_in($key, $value);
			} else {
				$val_str = self::where_clause_op($key, $value, $op);
			}
			if (!empty($val_str))	$str .= $val_str . $rel_str;
		}
		return trim($str, $rel_str);
	}
	
	static function where_clause_op($field, $value, $op='=') {
		$val_str = is_numeric($value) ? $value : "'$value'";
		return $field . $op . $val_str;
	}
	
	static function where_clause_in($field, $values) {
		if (empty($values)) {
			return "";
		}
		return "$field IN (" .  FunctionPool::array_to_str($values, ', ', "'") . ")";
	}
}
?>